package com.lz.sports.db

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Transaction
import androidx.room.Update
import com.lz.sports.bean.*

//考试检录
@Dao
interface ExamInspectDao {

    //插入检录组
    @Insert
    fun insertInspectGroup(inspectGroup: ExamInspectGroup): Long

    //更新组
    @Update
    fun updateInspectGroup(inspectGroup: ExamInspectGroup)

    @Update
    fun updateExamInspectList(inspectList: List<ExamInspect>)

    //插入检录列表
    @Insert
    fun insertInspectList(inspectList: List<ExamInspect>)

    //考生在该项目考点考区的检录次数
    @Query("select COUNT(*) from ExamInspect where examineeId = :examineeId and examPlanId = :examPlanId and projectId = :projectId and examTime = 0 and examAreaId = :examAreaId and examSiteId = :examSiteId")
    fun inspectCount(examineeId: Int, examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int): Long

    //查询检录组
    @Transaction
    @Query("select * from ExamInspectGroup where examPlanId = :examPlanId and projectId = :projectId and examAreaId = :examAreaId and examSiteId = :examSiteId")
    fun inspectGroup(examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int): List<ExamInspectGroup>

    //查询检录组未考大小
    @Query("select COUNT(*) from ExamInspectGroup where examPlanId = :examPlanId and projectId = :projectId and examTime = 0 and examAreaId = :examAreaId and examSiteId = :examSiteId")
    fun inspectGroupCount(examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int): Long

    //查询已检录未考的人数
    @Query("select COUNT(*) from ExamInspect where examPlanId = :examPlanId and projectId = :projectId and examTime = 0 and examAreaId = :examAreaId and examSiteId = :examSiteId")
    fun inspectCount(examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int): Long

    //查询项目组下的检录数据, 条件为学生和项目
    @Query("select * from ExamInspect where examPlanId = :examPlanId and projectId = :projectId and examTime = 0 and examAreaId = :examAreaId and examSiteId = :examSiteId and examineeId = :examineeId")
    fun examInspectList(examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int, examineeId: Int): List<ExamInspect>

    //查询检录
    @Transaction
    @Query("select ei.* from ExamInspect ei left join Examinee e on ei.examineeId = e.id where ei.examPlanId = :examPlanId and ei.examAreaId = :examAreaId and ei.examSiteId = :examSiteId and (e.studentName like '%' || :keyword || '%' or e.examineesNo like '%' || :keyword || '%' or e.idCard like '%' || :keyword || '%')")
    fun queryInspect(keyword: String, examPlanId: Int, examAreaId: Int, examSiteId: Int): List<ExamInspectWithInfo>

    @Transaction
    @Query("select * from ExamInspect where examPlanId = :examPlanId and examAreaId = :examAreaId and examSiteId = :examSiteId")
    fun queryInspect(examPlanId: Int, examAreaId: Int, examSiteId: Int): List<ExamInspectWithInfo>

    //查询考试成绩
    @Transaction
    @Query("select ei.* from ExamInspect ei left join Examinee e on ei.examineeId = e.id where ei.examTime > 0 and ei.examPlanId = :examPlanId and ei.examAreaId = :examAreaId and ei.examSiteId = :examSiteId and (e.studentName like '%' || :keyword || '%' or e.examineesNo like '%' || :keyword || '%' or e.idCard like '%' || :keyword || '%')")
    fun queryExamined(keyword: String, examPlanId: Int, examAreaId: Int, examSiteId: Int): List<ExaminedWithInfo>

    @Transaction
    @Query("select * from ExamInspect where examTime > 0 and examPlanId = :examPlanId and examAreaId = :examAreaId and examSiteId = :examSiteId")
    fun queryExamined(examPlanId: Int, examAreaId: Int, examSiteId: Int): List<ExaminedWithInfo>

    //查询检录组对应的检录信息
    @Transaction
    @Query("select * from ExamInspect where inspectGroupId = :inspectGroupId")
    fun queryInspect(inspectGroupId: Int): List<ExamInspectWithInfo>


    //查询所有未上传的考试信息
    @Query("select * from ExamInspect where examUploadStatus = -1 and examTime > 0 ")
    fun examUploadList(): List<ExamInspect>

    //查询所有未上传的检录信息
    @Query("select * from ExamInspect where uploadStatus = -1")
    fun uploadList(): List<ExamInspect>

    //查询报考照未传
    @Query("select * from ExamInspect where examPhotoUploadStatus = -1 and examType > 0 and uploadStatus = 0")
    fun examPhotoUploadList(): List<ExamInspect>

    //查询身份证照未传
    @Query("select * from ExamInspect where idCardPhotoUploadStatus = -1 and examType > 0 and uploadStatus = 0")
    fun idCardPhotoUploadList(): List<ExamInspect>

    //查询现场照未传
    @Query("select * from ExamInspect where scenePhotoUploadStatus = -1 and examType > 0 and uploadStatus = 0")
    fun scenePhotoUploadList(): List<ExamInspect>










    //清空考生表
    @Query("delete from ExamInspect")
    fun clearExamInspect()

    //清空考生表
    @Query("delete from ExamInspectGroup")
    fun clearExamInspectGroup()
}